
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimCommodityGroup_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimCommodityGroup_Update]
GO


create procedure dbo.p_DimCommodityGroup_Update (
	@CommodityGroupKey int,
	@CommodityGroupIdentifier varchar (20),
	@CommodityGroupName nvarchar (50),
	@ParentCommodityGroupKey int)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimCommodityGroupUpdate
        else
	    save transaction txnDimCommodityGroupUpdate

	set @Error = @@error
	if (@Error <> 0) goto cleanup

	if (not exists (select 1 
		          from dbo.DimCommodityGroup with (NOLOCK)
			  where (CommodityGroupKey = @ParentCommodityGroupKey)))
	begin

		set @Error = -3
		goto cleanup

	end		

	update dbo.DimCommodityGroup set CommodityGroupIdentifier = @CommodityGroupIdentifier,
					 CommodityGroupName = @CommodityGroupName,
					 ParentCommodityGroupKey = @ParentCommodityGroupKey
	  where (CommodityGroupKey = @CommodityGroupKey)	

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -2
	if (@Error <> 0) goto cleanup

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimCommodityGroupUpdate
		else
		if (@inTranCount = 0) commit transaction txnDimCommodityGroupUpdate

	end

	return @Error

end
GO

grant execute on dbo.p_DimCommodityGroup_Update  to [db_datawriter]
go
